package com.qin.EducationalManger.course.dao;


import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.qin.EducationalManger.course.model.CourseModel;
import com.qin.EducationalManger.course.model.StudentCourseModel;
import com.qin.EducationalManger.student.model.StudentModel;
import com.qin.EducationalManger.utils.JdbcUtils;


public class CourseDao {
	JdbcUtils jdbcUtils = new JdbcUtils();

	public static int insert(CourseModel model) {

		String sql = "insert into course (course_id,course_name,grade_point,time,teacher_id) values(?,?,?,?,?)";
		List<Object> vals = Arrays.asList(model.getCourseId(), model.getCourseName(), model.getGradePoint(),
				model.getTime(),model.getTeacherId());

		return JdbcUtils.update(sql, vals);

	}

	public static Integer delet(CourseModel model) {
		String sql = "delete from course where course_id = ?";
	
		List<Object> vals = Arrays.asList(model.getCourseId());

		return JdbcUtils.update(sql, vals);

	}
	

	
	//按照条件修改

	public static int update(CourseModel mode) {

		StringBuffer sql = new StringBuffer("update course set course_id="+mode.getCourseId()); // 储存sql语句
		List<Object> vals = new ArrayList<Object>(); // 用于存储 要修改的 内容

		String coursename = mode.getCourseName(); // 判断是否要对该值做出修改
		if (coursename != null && coursename.intern().isEmpty() == false) {
			sql.append(" ,course_name=?");
			vals.add(coursename);
		}

		Double gradePoint = mode.getGradePoint();
		if (gradePoint != null) {
			sql.append(",grade_point =?");
			vals.add(gradePoint);

		}
		String time = mode.getTime();
		if (time != null && time.intern().isEmpty() == false) {
			sql.append(",time =?");
			vals.add(time);
		}

	
		String teacherId = mode.getTeacherId();
		if(teacherId!=null&&teacherId.intern().isEmpty()==false) {
			sql.append(",teacher_id =?");
			vals.add(teacherId);
		}
		sql.append("where course_id = " + mode.getCourseId());

		return JdbcUtils.update(sql.toString(), vals);

	}
	
	//按照条件查询
	public  static List<CourseModel> select(CourseModel model) {
	StringBuffer sql = new StringBuffer("select * from course where 1=1 ");
	
	Map<String, String> fields = new HashMap<String, String>();
	fields.put("courseId", "course_id");
	fields.put("courseName", "course_name");
	fields.put("gradePoint", "grade_point");
	fields.put("time", "time");
	fields.put("teacherId", "teacher_id");

	
	
	String courseName =  model.getCourseName();
	
	List<Object> vals = appendWhere(sql, model);
	
	
	return  JdbcUtils.Query(sql.toString(), vals, CourseModel.class, fields);
	
		
	}
	
	private static List<Object>  appendWhere(StringBuffer sql, CourseModel model) {
		
		List<Object> vals = new ArrayList<Object>();
		String courseName = model.getCourseName();
		if(courseName!=null&&courseName.intern().isEmpty()==false) {
			sql.append(" and course_name like ?");
			vals.add(courseName);
		}
		String courseId = model.getCourseId();
		if(courseId!=null&&courseId.intern().isEmpty()==false) {
			sql.append(" and course_id = ?");
			vals.add(courseId);
		}
		Double gradePoint =model.getGradePoint();
		if(gradePoint!=null) {
			sql.append(" and grade_point =?");
			vals.add(gradePoint);
			
		}
		String time = model.getTime();
		if(time!=null&&time.intern().isEmpty()==false) {
			sql.append(" and time like ?");
			vals.add(time);	
		}
	
	
		String teacherId = model.getTeacherId();
		if(teacherId!=null&&teacherId.intern().isEmpty()==false) {
			
			sql.append(" and teacher_id = ?");
			vals.add(teacherId);
		}
		
		return vals;
		
		
	}

	
	public  static List<StudentCourseModel> select (String [] courseIds){
		
		StringBuffer sql = new StringBuffer(" select c.course_id as course_id,course_name,grade_point,time,teacher_name,title from course as c\r\n" + 
				" inner join teacher as t\r\n" + 
				" on c.teacher_id=t.teacher_id\r\n" + 
				" where c.course_id in (?");
		
		
				
		List<Object> vals = new ArrayList<Object>();
		vals.add(courseIds[0]);
		for(int i =1;i<courseIds.length;i++) {
			sql.append(",?");
			vals.add(courseIds[i]);
		}

		
		sql.append(")");
	
		
		
		Map<String, String> fields = new HashMap<String, String>();
	
		fields.put("courseId", "c.course_id");
		fields.put("courseName", "course_name");
		fields.put("gradePoint", "grade_point");
		fields.put("time", "time");
		fields.put("teacherName", "teacher_name");
		fields.put("title", "title");
		
		
		return JdbcUtils.Query(sql.toString(), vals, StudentCourseModel.class, fields);
		
	}
	
	// 查询信息总条数
		public static Integer  selectCount(CourseModel model) {
			String courseId = model.getCourseId();
			String courseName = model.getCourseName();
			List <Object> vals = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer("select count(course_id) as count from course where 1=1");
			if(courseId!=null&&courseId.intern().isEmpty()==false) {
				sql.append(" and course_id like ?");
				vals.add(courseId);
			}
			
			
			if(courseName!=null&&courseName.intern().isEmpty()==false) {
				sql.append(" and course_name like ?");
				vals.add(courseName);
			}
		
		
			 
			
			return JdbcUtils.selectCount(sql.toString(),vals);
			
		}
	
	
}
